package com.quectel.util.db;

import com.quectel.util.ex.common.DatabaseSqlException;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by CL on 2016/10/14.
 */
@Slf4j
public class DBKit {


    private DataSource dataSource;

    private QueryRunner queryRunner;

    private DBKit() {

    }

    /**
     * 得到帮助类
     *
     * @param type     数据库类型
     * @param url      jdbc连接所需的url
     * @param user     用户名
     * @param password 密码
     * @return
     */
    public static DBKit getInstance(DBType type, String url, String user, String password) {
        DBKit kit = new DBKit();
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        dataSource.setPoolName("HikariPool");
        setOtherParams(type, dataSource);
        kit.dataSource = dataSource;
        kit.queryRunner = new QueryRunner(dataSource);
        return kit;
    }

    /**
     * 填充对象
     *
     * @param type
     * @param datasource
     */
    private static void setOtherParams(DBType type, HikariDataSource datasource) {
        // 最小连接数
        datasource.setMinimumIdle(8);
        // 最大连接数
        datasource.setMaximumPoolSize(32);
        // 默认连接自动提交
        datasource.setAutoCommit(true);
        // 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 默认:30秒
        datasource.setConnectionTimeout(30000);
        // 一个连接idle状态的最大时长（毫秒），超时则被释放（retired），默认:10分钟
        datasource.setIdleTimeout(600000);
        // 多久进行一次test query
        datasource.setKeepaliveTime(60000);

        if (DBType.MYSQL.equals(type)) {
            datasource.setDriverClassName("com.mysql.cj.jdbc.Driver");
            datasource.setConnectionInitSql("set names utf8mb4;");
            datasource.setConnectionTestQuery("select 1");
            // 一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），默认:30分钟 1800000ms，建议设置比数据库超时时长少60秒，参考MySQL wait_timeout参数（show variables like '%timeout%';） -->
            datasource.setMaxLifetime(28740000);

            // MySQL特有的优化配置
            datasource.addDataSourceProperty("cachePrepStmts", true);
            datasource.addDataSourceProperty("prepStmtCacheSize", 250);
            datasource.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
            datasource.addDataSourceProperty("useServerPrepStmts", true);
            datasource.addDataSourceProperty("useLocalSessionState", true);
            datasource.addDataSourceProperty("rewriteBatchedStatements", true);
            datasource.addDataSourceProperty("cacheResultSetMetadata", true);
            datasource.addDataSourceProperty("cacheServerConfiguration", true);
            datasource.addDataSourceProperty("elideSetAutoCommits", true);
            datasource.addDataSourceProperty("maintainTimeStats", false);

        } else if (DBType.PG.equals(type)) {
            datasource.setDriverClassName("org.postgresql.Driver");
            datasource.setConnectionTestQuery("select 1");
        } else if (DBType.ORACLE.equals(type)) {
            datasource.setDriverClassName("oracle.jdbc.OracleDriver");
            datasource.setConnectionTestQuery("select 1 from dual");
        } else if (DBType.SQLSERVER.equals(type)) {
            datasource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            datasource.setConnectionTestQuery("select 1");
        }
    }

    /**
     * 是否是可用的 连接池有自动重连机制所以永远都是激活的
     *
     * @return
     */
    public boolean isActive() {
        return true;
    }

    public Connection getConnection() throws SQLException {
        return this.dataSource.getConnection();
    }

    /**
     * 创建一张表
     *
     * @param createTableSql 创建表的句子  例如create table user(id varchar(255) primary key,name varchar(255),age int)
     * @throws Exception
     */
    public void createTable(String createTableSql) {
        try {
            queryRunner.execute(createTableSql);
        } catch (Exception e) {
            throw new DatabaseSqlException("Create table failed");
        }
    }

    /**
     * 增删改
     *
     * @param sql    增删改的句子  例如insert into user(id,name,age) values(?,?,?)
     * @param params 值["1","zhang",10]
     * @throws Exception
     */
    public void executeUpdate(String sql, Object[] params) {
        try {
            queryRunner.execute(sql, params);
        } catch (Exception e) {
            throw new DatabaseSqlException("Execute sql failed");
        }
    }

    /**
     * 批量增删改
     *
     * @param sql    增删改的句子  例如insert into user(id,name,age) values(?,?,?)
     * @param params 值[["1","zhang",10],["1","cao",100]]
     * @throws Exception
     */
    public void batchExecuteUpdate(String sql, Object[][] params) {
        try {
            queryRunner.batch(sql, params);
        } catch (Exception e) {
            throw new DatabaseSqlException("Execute sql failed");
        }
    }

    /**
     * 查询一条记录或者多条记录
     *
     * @param sql    查询的句子  例如select * from user where id=?
     * @param params 值["1"]
     * @throws Exception
     */
    public List<Map<String, Object>> find(String sql, Object[] params) {
        try {
            return queryRunner.query(sql, new ResultSetHandler<List<Map<String, Object>>>() {
                @Override
                public List<Map<String, Object>> handle(ResultSet rs) throws SQLException {
                    List<Map<String, Object>> result = new ArrayList<>();
                    while (rs.next()) {
                        Map<String, Object> dataMap = new LinkedHashMap<>();
                        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                            Object object = rs.getObject(i + 1);
                            dataMap.put(rs.getMetaData().getColumnName(i + 1), object);
                        }
                        result.add(dataMap);
                    }
                    return result;
                }
            }, params);
        } catch (Exception e) {
            throw new DatabaseSqlException("Execute query sql failed");
        }
    }

    /**
     * 查询一条记录或者多条记录 简单的泛型 日期类的无法反射
     *
     * @param sql    查询的句子  例如select * from user where id=?
     * @param params 值["1"]
     * @param clazz  要转换的泛型
     * @throws Exception
     */
    public <T> List<T> find(String sql, Object[] params, Class<T> clazz) {
        try {
            return queryRunner.query(sql, new BeanListHandler<>(clazz), params);
        } catch (Exception e) {
            throw new DatabaseSqlException("Execute query sql failed");
        }
    }

    /**
     * 关闭连接池
     */
    public void close() {
        if (dataSource instanceof HikariDataSource) {
            ((HikariDataSource) dataSource).close();
        }
    }


}
